package com.developer.sqliteexample.model;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.developer.sqliteexample.model.entity.Employee;
import com.developer.sqliteexample.util.Util;

public final class DbContext {

	private static DbContext INSTANCE;

	private final DbHelper dbHelper;

	private DbContext(Context context) {
		this.dbHelper = new DbHelper(context);
	}

	public static DbContext getInstance(Context context) {
		if (INSTANCE == null) INSTANCE = new DbContext(context);
		return INSTANCE;
	}

	public boolean insertEmployee(Employee employee) {
		boolean result = false;
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.COLUMN_FIRST_NAME, employee.getFirstName());
		contentValues.put(DbHelper.COLUMN_LAST_NAME, employee.getLastName());
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		long id = database.insert(DbHelper.TABLE_NAME, null, contentValues);
		if (id != -1) {
			employee.setId(id);
			result = true;
		}
		return result;
	}

	public boolean updateEmployeeName(String oldName, String newName) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.COLUMN_FIRST_NAME, newName);
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		final String whereClause = DbHelper.COLUMN_FIRST_NAME + "=?";
		final String[] whereArgs = { oldName };
		int affectedRows = database.update(DbHelper.TABLE_NAME, contentValues, whereClause, whereArgs);
		return affectedRows > 0;
	}

	public boolean deleteEmployee(long id) {
		SQLiteDatabase database = dbHelper.getWritableDatabase();
		final String whereClause = DbHelper.COLUMN_ID + "=?";
		final String[] whereArgs = { String.valueOf(id) };
		int affectedRows = database.delete(DbHelper.TABLE_NAME, whereClause, whereArgs);
		return affectedRows > 0;
	}

	public Employee findById(long id) {
		Employee employee = null;
		SQLiteDatabase database = dbHelper.getReadableDatabase();
		final String[] columns = { DbHelper.COLUMN_ID, DbHelper.COLUMN_FIRST_NAME, DbHelper.COLUMN_LAST_NAME };
		final String selection = DbHelper.COLUMN_ID + "=?";
		final String[] selectionArgs = { String.valueOf(id) };
		Cursor cursor = database.query(DbHelper.TABLE_NAME, columns, selection, selectionArgs, null, null, null);
		while (cursor.moveToNext()) {
			employee = new Employee();
			employee.setId(cursor.getLong(cursor.getColumnIndex(DbHelper.COLUMN_ID)));
			employee.setFirstName(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_FIRST_NAME)));
			employee.setLastName(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_LAST_NAME)));
		}
		return employee;
	}

	public List<Employee> getAllEmployees() {
		List<Employee> employees = new ArrayList<Employee>();
		SQLiteDatabase database = dbHelper.getReadableDatabase();
		final String[] columns = { DbHelper.COLUMN_ID, DbHelper.COLUMN_FIRST_NAME, DbHelper.COLUMN_LAST_NAME };
		Cursor cursor = database.query(DbHelper.TABLE_NAME, columns, null, null, null, null, null);
		while (cursor.moveToNext()) {
			Employee employee = new Employee();
			employee.setId(cursor.getLong(cursor.getColumnIndex(DbHelper.COLUMN_ID)));
			employee.setFirstName(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_FIRST_NAME)));
			employee.setLastName(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_LAST_NAME)));
			employees.add(employee);
		}
		return employees;
	}

	private static class DbHelper extends SQLiteOpenHelper {

		private static final String DATABASE_NAME = "EMPLOYEES";
		private static final int DATABASE_VERSION = 2;
		private static final String TABLE_NAME = "EMPLOYEE";
		private static final String COLUMN_ID = "_id";
		private static final String COLUMN_FIRST_NAME = "FIRST_NAME";
		private static final String COLUMN_LAST_NAME = "LAST_NAME";
		private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_FIRST_NAME + " VARCHAR(255), "
				+ COLUMN_LAST_NAME + " VARCHAR(255))";
		private static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;

		private final Context context;

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE);
			Util.showMessage(context, "Database created");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Util.showMessage(context, "Database dropped");
			db.execSQL(DROP_TABLE);
			onCreate(db);
		}

	}

}
